﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace RCSP.DataAccess
{
    public class RCSPDatabaseAccess
    {
        #region MEMBER Variables:

        private SqlConnection connection;

        #endregion

        #region PROPERTIES:

        public SqlConnection Connection
        {
            get { return this.connection; }
            set { this.connection = value; }
        }


        #endregion

        #region CONSTRUCTORS:

        public RCSPDatabaseAccess()
        {
            this.connection = RCSPConnectionManager.GetConnection();
        }

        public RCSPDatabaseAccess(string connectionString)
        {
            RCSPConnectionManager.ReplaceCurrentInstance(connectionString);
            this.connection = RCSPConnectionManager.GetConnection();
        }

        #endregion

        #region METHODS:

        /// <summary>
        /// Create new SqlCommand object that has some values:
        /// - command text is QUERY parameter
        /// - connection object is the current connection
        /// - command type is CommandType.Text
        /// </summary>
        /// <param name="query">Command text</param>
        /// <returns>New SqlCommand object</returns>
        protected SqlCommand CreateNewSqlCommand(string query)
        {
            SqlCommand command = new SqlCommand(query, this.connection);
            command.CommandType = CommandType.Text;
            return command;
        }

        /// <summary>
        /// This method will execute the COMMAND object
        /// </summary>
        /// <param name="command">COMMAND object</param>
        /// <returns>Number of AFFECTED rows in database</returns>
        protected int ExecuteNonQuery(SqlCommand command)
        {
            int rowsAffected = 0;
            try
            {
                command.Connection.Open();
                rowsAffected = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                command.Connection.Close();
            }
            return rowsAffected;
        }

        /// <summary>
        /// This method will execute the SQL non-query string parameter 
        /// </summary>
        /// <param name="query">SQL non-query string (INSERT/UPDATE/DELETE)</param>
        /// <returns>Number of AFFECTED rows in database</returns>
        protected int ExecuteNonQuery(string query)
        {
            SqlCommand command = this.CreateNewSqlCommand(query);
            return (this.ExecuteNonQuery(command));
        }

        protected SqlDataReader ExecuteReader(SqlCommand command)
        {
            SqlDataReader dataReader = null;
            try
            {
                command.Connection.Open();
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                command.Connection.Close();

            }

            return dataReader;
        }

        protected SqlDataReader ExecuteReader(string query)
        {
            SqlCommand command = this.CreateNewSqlCommand(query);
            return (this.ExecuteReader(command));
        }

        /// <summary>
        /// Execute the query of COMMAND object and fill the TABLE_NAME in the DATASET object
        /// </summary>
        /// <param name="command">COMMAND object</param>
        /// <param name="tableName">NAME of table will be filled in return DATASET object</param>
        /// <returns>Result DATASET object</returns>
        protected DataSet ExecuteQuery(SqlCommand command, string tableName)
        {
            DataSet dataSet = new DataSet();
            try
            {
                command.Connection.Open();
                SqlDataAdapter dataAdapter = new SqlDataAdapter();
                dataAdapter.SelectCommand = command;
                dataAdapter.Fill(dataSet, tableName);
            }
            catch (Exception ex)
            {
                //Log.Logger.LogError(ex.ToString());
            }
            finally
            {
                command.Connection.Close();
            }
            return dataSet;
        }

        protected DataSet ExecuteQuery(SqlCommand command)
        {
            DataSet dataSet = new DataSet();
            try
            {
                command.Connection.Open();
                SqlDataAdapter dataAdapter = new SqlDataAdapter();
                dataAdapter.SelectCommand = command;
                dataAdapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                //Log.Logger.LogError(ex.ToString());
            }
            finally
            {
                command.Connection.Close();
            }
            return dataSet;
        }

        protected DataSet ExecuteQuery(string query, string tableName)
        {
            DataSet datset = new DataSet();
            SqlCommand command = this.CreateNewSqlCommand(query);
            return this.ExecuteQuery(command, tableName);
        }
        protected DataSet ExecuteQuery(string query)
        {
            DataSet datset = new DataSet();
            SqlCommand command = this.CreateNewSqlCommand(query);
            return this.ExecuteQuery(command);
        }

        /// <summary>
        /// Eliminate SQL injection
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static string EliminateSQLInjection(string parameter)
        {
            parameter = parameter.Replace("'", "''");
            parameter = parameter.Replace("[", "[[]");
            parameter = parameter.Replace("%", "[%]");
            parameter = parameter.Replace("_", "[_]");
            return parameter;
        }

        /// <summary>
        /// Count number of rows of a command
        /// </summary>
        /// <param name="command">Command to execute</param>
        /// <param name="countField">Field to get the total number in DataReader</param>
        /// <returns></returns>
        public int CountRows(SqlCommand command)
        {
            int totalRows = 0;
            SqlDataReader reader = this.ExecuteReader(command);
            if ((null != reader) && reader.Read())
            {
                totalRows = reader.GetInt32(0);
                reader.Close();
            }
            return totalRows;

        }

        public int CountRows(string query)
        {
            SqlCommand command = this.CreateNewSqlCommand(query);
            return this.CountRows(command);
        }
        #endregion 
    }
}